package com.clqteam.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.clqteam.dao.OrderDao;
import com.clqteam.model.Order;
import com.clqteam.util.JDBCutil;

import net.sf.json.JSONObject;
/**
 * 
 * @author ZHX
 *
 */
public class OrderDaoImpl implements OrderDao{
	
	@Override
	public int getAllCountOrder() {
		int cou = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(s_order.oId) AS total FROM s_order");
		Connection con = JDBCutil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());	
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				cou = rs.getInt("total");
			}
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}			
		return cou;
	}

	
	
	@Override
	public int getCountOrderByClass(int oClassId) {
		int cou = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(s_order.oId) AS total");
		sql.append(" FROM s_order WHERE s_order.oClassId = ?");
		Connection con = JDBCutil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, oClassId);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				cou = rs.getInt("total");
			}
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		
		return cou;
	}
	
	@Override
	public List<Order> getAllOrder(int page, int rows) {
		List<Order> orders = new  ArrayList<Order>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT s_order.oId,s_order.oClassId,s_orderclass.oClassName,s_order.oMoney");
		sql.append(" ,s_order.eId,s_employee.eName,s_order.oTime,s_order.vId FROM s_order");
		sql.append(" INNER JOIN s_orderclass ON s_orderclass.oClassId = s_order.oClassId");
		sql.append(" INNER JOIN s_employee ON s_employee.eId = s_order.eId");
		sql.append(" LIMIT ?,?");		
		Connection con = JDBCutil.getConnection();		
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Order order = new Order();
				order.seteId(rs.getInt("eId"));
				order.seteName(rs.getString("eName"));
				order.setoClass(rs.getString("oClassName"));
				order.setoClassId(rs.getInt("oClassId"));
				order.setoId(rs.getInt("oId"));
				order.setoMoney(rs.getDouble("oMoney"));
				order.setoTime(rs.getString("oTime"));
				order.setvId(rs.getInt("vId"));
				orders.add(order);				
			}
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return orders;
	}

	@Override
	public List<Order> getOrderByClass(int oClassId,int page,int rows) {
		List<Order> orders = new  ArrayList<Order>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT s_order.oId,s_order.oClassId,s_orderclass.oClassName,s_order.oMoney");
		sql.append(" ,s_order.eId,s_employee.eName,s_order.oTime,s_order.vId FROM s_order");
		sql.append(" INNER JOIN s_orderclass ON s_orderclass.oClassId = s_order.oClassId");
		sql.append(" INNER JOIN s_employee ON s_employee.eId = s_order.eId");
		sql.append(" WHERE s_order.oClassId = ? LIMIT ?,?");		
		Connection con = JDBCutil.getConnection();		
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, oClassId);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Order order = new Order();
				order.seteId(rs.getInt("eId"));
				order.seteName(rs.getString("eName"));
				order.setoClass(rs.getString("oClassName"));
				order.setoClassId(rs.getInt("oClassId"));
				order.setoId(rs.getInt("oId"));
				order.setoMoney(rs.getDouble("oMoney"));
				order.setoTime(rs.getString("oTime"));
				order.setvId(rs.getInt("vId"));
				orders.add(order);				
			}
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return orders;
	}



	@Override
	public int deleteOrderByOId(int oId) {
		int flag = -1;
		Connection con =JDBCutil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append(" DELETE FROM s_order WHERE oId = ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, oId);
			flag = pst.executeUpdate();	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}



	@Override
	public int getCountInfoByKey(String key) {		
		int total = 0;
		Connection con = JDBCutil.getConnection();
		try {
			int k = Integer.parseInt(key);
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT COUNT(s_order.oId) AS total FROM s_order");
			sql.append(" INNER JOIN s_employee ON s_order.eId = s_employee.eId");
			sql.append(" INNER JOIN s_orderclass ON s_order.oClassId = s_orderclass.oClassId");
			sql.append(" WHERE (s_order.oTime LIKE ? OR s_order.oId = ? OR s_order.vId = ?)");
			try {
				PreparedStatement pst = con.prepareStatement(sql.toString());
				pst.setString(1, "%"+key+"%");
				pst.setInt(2, k);
				pst.setInt(3, k);
				ResultSet rs = pst.executeQuery();
				if(rs.next()){
					total = rs.getInt("total");
				}
				JDBCutil.closeJDBC(pst, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		} catch (NumberFormatException e) {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT COUNT(s_order.oId) AS total FROM s_order");
			sql.append(" INNER JOIN s_employee ON s_order.eId = s_employee.eId");
			sql.append(" INNER JOIN s_orderclass ON s_order.oClassId = s_orderclass.oClassId");
			sql.append(" WHERE(s_orderclass.oClassName LIKE ? ");
			sql.append(" OR s_employee.eName LIKE ?)");
			try {
				PreparedStatement pst = con.prepareStatement(sql.toString());				
				pst.setString(1, "%"+key+"%");
				pst.setString(2, "%"+key+"%");
				ResultSet rs = pst.executeQuery();
				if(rs.next()){
					total = rs.getInt("total");
				}
				JDBCutil.closeJDBC(pst, con);
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}			
		}		
		return total;
	}



	@Override
	public List<Order> getInfoByKey(String key,int page,int rows) {
		List<Order> orders = new ArrayList<Order>();
		Connection con = JDBCutil.getConnection();
		try {
			int k = Integer.parseInt(key);
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT s_order.oId,s_order.oClassId,s_orderclass.oClassName,s_order.oMoney");
			sql.append(" ,s_order.eId,s_employee.eName,s_order.oTime,s_order.vId FROM s_order");
			sql.append(" INNER JOIN s_orderclass ON s_orderclass.oClassId = s_order.oClassId");
			sql.append(" INNER JOIN s_employee ON s_employee.eId = s_order.eId");
			sql.append(" WHERE (s_order.oTime LIKE ? OR s_order.oId = ? OR s_order.vId = ?)");
			sql.append(" LIMIT ?,?");	
			try {
				PreparedStatement pst = con.prepareStatement(sql.toString());
				pst.setString(1, "%"+key+"%");
				pst.setInt(2, k);
				pst.setInt(3, k);
				pst.setInt(4, (page-1)*rows);
				pst.setInt(5, rows);
				ResultSet rs = pst.executeQuery();
				while(rs.next()){
					Order order = new Order();
					order.seteId(rs.getInt("eId"));
					order.seteName(rs.getString("eName"));
					order.setoClass(rs.getString("oClassName"));
					order.setoClassId(rs.getInt("oClassId"));
					order.setoId(rs.getInt("oId"));
					order.setoMoney(rs.getDouble("oMoney"));
					order.setoTime(rs.getString("oTime"));
					order.setvId(rs.getInt("vId"));
					orders.add(order);				
				}
				JDBCutil.closeJDBC(pst, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		} catch (NumberFormatException e) {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT s_order.oId,s_order.oClassId,s_orderclass.oClassName,s_order.oMoney");
			sql.append(" ,s_order.eId,s_employee.eName,s_order.oTime,s_order.vId FROM s_order");
			sql.append(" INNER JOIN s_orderclass ON s_orderclass.oClassId = s_order.oClassId");
			sql.append(" INNER JOIN s_employee ON s_employee.eId = s_order.eId");
			sql.append(" WHERE(s_orderclass.oClassName LIKE ? ");
			sql.append(" OR s_employee.eName LIKE ?)");
			sql.append(" LIMIT ?,?");	
			try {
				PreparedStatement pst = con.prepareStatement(sql.toString());				
				pst.setString(1, "%"+key+"%");
				pst.setString(2, "%"+key+"%");
				pst.setInt(3, (page-1)*rows);
				pst.setInt(4, rows);
				ResultSet rs = pst.executeQuery();
				while(rs.next()){
					Order order = new Order();
					order.seteId(rs.getInt("eId"));
					order.seteName(rs.getString("eName"));
					order.setoClass(rs.getString("oClassName"));
					order.setoClassId(rs.getInt("oClassId"));
					order.setoId(rs.getInt("oId"));
					order.setoMoney(rs.getDouble("oMoney"));
					order.setoTime(rs.getString("oTime"));
					order.setvId(rs.getInt("vId"));
					orders.add(order);				
				}
				JDBCutil.closeJDBC(pst, con);
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}			
		}		
		return orders;
	}

}
